﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EntitiesLayer;
using System.Data;
using System.Data.SqlClient;

namespace DataAccessLayer
{
    public class SqlManager : Bridge
    {
        private readonly String connectionString = @"Data Source=(localdb)\Projects;Initial Catalog=QuidditchDatabase;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False";

        #region Properties

        /// <summary>
        /// Liste des Joueurs
        /// </summary>
        public IList<Joueur> Players
        {
            get 
            {
                return Select("Select * from Player", ReadPlayer).ConvertAll(o => o as Joueur);
            }
        }

        public IList<Reservation> Reservations
        {
            get 
            {
                return Select("Select * from Reservation", ReadReservation).ConvertAll(o => o as Reservation);
            }
        }

        /// <summary>
        /// Liste des Equipes
        /// </summary>
        public IList<Equipe> Teams
        {
            get 
            {
                return Select("Select * from Equipe", ReadEquipe).ConvertAll(o => o as Equipe);
            }
        }

        /// <summary>
        /// Liste des Coupes
        /// </summary>
        public IList<Coupe> Cups
        {
            get
            {
                return Select("Select * from Coupe", ReadCup).ConvertAll(o => o as Coupe);
            }
        }

        /// <summary>
        /// Liste des Stades
        /// </summary>
        public IList<Stade> Stadiums
        {
            get
            {
                return Select("Select * from Stade", ReadStade).ConvertAll(o => o as Stade);
            }
        }

        /// <summary>
        /// Liste des Matches
        /// </summary>
        public IList<Match> Matches
        {
            get 
            {
                return Select("Select * from Match", ReadMatch).ConvertAll(o => o as Match);
            }
        }

        /// <summary>
        /// Liste des Spectateurs
        /// </summary>
        public IList<Spectateur> Spectateurs
        {
            get
            {
                return Select("Select * from Spectateur", ReadSpectateur).ConvertAll(o => o as Spectateur);
            }
        }

        #endregion

        /// <summary>
        /// Requête Select sur une des tables de la base de données
        /// </summary>
        /// <param name="request"> Requête SELECT à effectuer </param>
        /// <param name="func"> Fonction permettant de construire l'entité correspondant à la requête </param>
        /// <returns> La liste des entités sous forme d'OBJECT. </returns>
        private List<object> Select(String request, Func<SqlDataReader,object> func)
        {
            List<object> listToFill = new List<object>();

            using(SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                SqlCommand command = new SqlCommand(request, connection);

                SqlDataReader reader = command.ExecuteReader();

                while(reader.Read())
                {
                    listToFill.Add(func(reader));
                }

                connection.Close();
            }

            return listToFill;
        }

        #region INSERT
        /// <summary>
        /// Requête Insert sur une des tables de la base de données
        /// </summary>
        /// <param name="obj"> Objet à insérer en base de donnée</param>
        /// <param name="nomTable">Nom de la table de la base de donnée</param>
        /// <returns></returns>
        public int Insert(Object obj, String nomTable)
        {
            int nbLigne = 0;
            String request = String.Empty;

            DataTableGeneric generic = new DataTableGeneric();
            SqlDataAdapter adapter;
            SqlCommandBuilder builder;
            DataTable dt;

            using (SqlConnection connection = new SqlConnection(connectionString)) 
            { 
                switch(nomTable.ToUpper())
                {
                    case "COUPE":
                        #region Insert Coupe
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesCoupe() + " FROM " + generic.FromCoupe(), connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForCoupe("SELECT " + generic.ValuesCoupe() + " FROM " + generic.FromCoupe());
                        dt.Rows.Add(DataTableGeneric.BuildRowByCoupe((Coupe)obj));
                        #endregion
                        break;

                    case "PLAYER":
                        #region Insert Joueur
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesPlayer() + " FROM " + generic.FromPlayer(), connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForPlayer("SELECT " + generic.ValuesPlayer() + " FROM " + generic.FromPlayer());
                        dt.Rows.Add(DataTableGeneric.BuildRowByJoueur((Joueur)obj));
                        #endregion
                        break;

                    case "EQUIPE":
                        #region Insert Equipe
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesEquipe() + " FROM " + generic.FromEquipe(), connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForTeams("SELECT " + generic.ValuesEquipe() + " FROM " + generic.FromEquipe());
                        dt.Rows.Add(DataTableGeneric.BuildRowByEquipe((Equipe)obj));
                        #endregion
                        break;

                    case "STADE":
                        #region Insert Stade
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesStade() + " FROM " + generic.FromStade(), connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForStade("SELECT " + generic.ValuesStade() + " FROM " + generic.FromStade());
                        dt.Rows.Add(DataTableGeneric.BuildRowByStade((Stade)obj));
                        #endregion
                        break;

                    case "RESERVATION":
                        #region Insert Reservation
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesReservation() + " FROM " + generic.FromReservation(), connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForReservation("SELECT " + generic.ValuesReservation() + " FROM " + generic.FromReservation());
                        dt.Rows.Add(DataTableGeneric.BuildRowByReservation((Reservation)obj));
                        #endregion
                        break;

                    case "MATCH":
                        #region Insert Match
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesMatch() + " FROM " + generic.FromMatch(), connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForMatch("SELECT " + generic.ValuesMatch() + " FROM " + generic.FromMatch());
                        dt.Rows.Add(DataTableGeneric.BuildRowByMatch((Match)obj));
                        #endregion
                        break;

                    case "SPECTATEUR":
                        #region Insert Match
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesSpectateur() + " FROM " + generic.FromSpectateur(), connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForSpectator("SELECT " + generic.ValuesSpectateur() + " FROM " + generic.FromSpectateur());
                        dt.Rows.Add(DataTableGeneric.BuildRowBySpectateur((Spectateur)obj));
                        #endregion
                        break;

                    default:
                        return 0;
                }

                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();

                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                nbLigne = adapter.Update(dt);

                return nbLigne;
            }
        }
        #endregion

        #region UPDATE
        /// <summary>
        /// Requête Update sur une des tables de la base de données
        /// </summary>
        /// <param name="obj"> Objet à mettre à jour en base de donnée</param>
        /// <param name="nomTable">Nom de la table de la base de donnée</param>
        /// <returns></returns>
        public int Update(Object obj, String nomTable)
        {
            int nbLigne = 0;
            String request = String.Empty;

            DataTableGeneric generic = new DataTableGeneric();
            SqlDataAdapter adapter;
            SqlCommandBuilder builder;
            DataTable dt;
            bool finish = false;

            using (SqlConnection connection = new SqlConnection(connectionString)) 
            { 
                switch(nomTable.ToUpper())
                {
                    case "COUPE":
                        #region Coupe
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesCoupe() + " FROM " + generic.FromCoupe() + " WHERE ID = " + ((Coupe)obj).ID, connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForCoupe("SELECT " + generic.ValuesCoupe() + " FROM " + generic.FromCoupe() + " WHERE ID = " + ((Coupe)obj).ID);
                        if (dt.Rows.Count == 1) 
                        {
                            dt.Rows[0]["NOM"] = ((Coupe)obj).Name;
                            dt.Rows[0]["ANNEE"] = ((Coupe)obj).Year;
                            finish = true;
                        }
                        #endregion
                        break;

                    case "PLAYER":
                        #region Joueur
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesPlayer() + " FROM " + generic.FromPlayer() + " WHERE ID = " + ((Joueur)obj).ID, connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForPlayer("SELECT " + generic.ValuesPlayer() + " FROM " + generic.FromPlayer() + " WHERE ID = " + ((Joueur)obj).ID);
                        if (dt.Rows.Count == 1) 
                        {
                            dt.Rows[0]["NOM"] = ((Joueur)obj).Nom;
                            dt.Rows[0]["PRENOM"] = ((Joueur)obj).Prenom;
                            dt.Rows[0]["DATENAISSANCE"] = ((Joueur)obj).DateNaissance;
                            dt.Rows[0]["POSTE"] = ((Joueur)obj).Poste;
                            dt.Rows[0]["NOMBRESELECTIONS"] = ((Joueur)obj).NbSelection;
                            dt.Rows[0]["SCORE"] = ((Joueur)obj).Score;
                            dt.Rows[0]["EQUIPEID"] = ((Joueur)obj).EquipeId;
                            finish = true;
                        }
                        #endregion
                        break;

                    case "EQUIPE":
                        #region Equipe
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesEquipe() + " FROM " + generic.FromEquipe() + " WHERE ID = " + ((Equipe)obj).ID, connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForTeams("SELECT " + generic.ValuesEquipe() + " FROM " + generic.FromEquipe() + " WHERE ID = " + ((Equipe)obj).ID);
                        if (dt.Rows.Count == 1) 
                        {
                            dt.Rows[0]["NOM"] = ((Equipe)obj).Nom;
                            dt.Rows[0]["PAYS"] = ((Equipe)obj).Pays;
                            finish = true;
                        }
                        #endregion
                        break;

                    case "STADE":
                        #region Stade
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesStade() + " FROM " + generic.FromStade() + " WHERE ID = " + ((Stade)obj).ID, connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForStade("SELECT " + generic.ValuesStade() + " FROM " + generic.FromStade() + " WHERE ID = " + ((Stade)obj).ID);
                        if (dt.Rows.Count == 1) 
                        {
                            dt.Rows[0]["NOM"] = ((Stade)obj).Nom;
                            dt.Rows[0]["ADRESSE"] = ((Stade)obj).Addresse;
                            dt.Rows[0]["NOMBREPLACES"] = ((Stade)obj).NbPlaces;
                            dt.Rows[0]["POURCENTAGECOMISSION"] = ((Stade)obj).PourcentageComission;
                            finish = true;
                        }
                        #endregion
                        break;

                    case "RESERVATION":
                        #region Reservation
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesReservation() + " FROM " + generic.FromReservation() + " WHERE ID = " + ((Reservation)obj).ID, connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForReservation("SELECT " + generic.ValuesReservation() + " FROM " + generic.FromReservation() + " WHERE ID = " + ((Reservation)obj).ID);
                        if (dt.Rows.Count == 1) 
                        {
                            dt.Rows[0]["MATCH"] = ((Reservation)obj).Match;
                            dt.Rows[0]["COUPE"] = ((Reservation)obj).IDCoupe;
                            dt.Rows[0]["SPECTATEUR"] = ((Reservation)obj).Spectateur;
                            dt.Rows[0]["NOMBREPLACESRESERVEES"] = ((Reservation)obj).NombrePlacesReserves;
                            finish = true;
                        }
                        #endregion
                        break;

                    case "MATCH":
                        #region Match
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesMatch() + " FROM " + generic.FromMatch() + " WHERE ID = " + ((Match)obj).ID, connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForMatch("SELECT " + generic.ValuesMatch() + " FROM " + generic.FromMatch() + " WHERE ID = " + ((Match)obj).ID);
                        if (dt.Rows.Count == 1) 
                        {
                            dt.Rows[0]["DATE"] = ((Match)obj).Date;
                            dt.Rows[0]["PRIX"] = ((Match)obj).Prix;
                            dt.Rows[0]["COUPEID"] = ((Match)obj).CoupeID;
                            dt.Rows[0]["EQUIPEDOMICILE"] = ((Match)obj).EquipeDomicile.ID;
                            dt.Rows[0]["EQUIPEVISITEUSE"] = ((Match)obj).EquipeVisiteuse.ID;
                            dt.Rows[0]["SCOREEQUIPEDOMICILE"] = ((Match)obj).ScoreEquipeDomicile;
                            dt.Rows[0]["SCOREEQUIPEVISITEUSE"] = ((Match)obj).ScoreEquipeVisiteuse;
                            dt.Rows[0]["STADE"] = ((Match)obj).Stade.ID;
                            finish = true;
                        }
                        #endregion
                        break;

                    case "SPECTATEUR":
                        #region Spectateur
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesSpectateur() + " FROM " + generic.FromSpectateur() + " WHERE ID = " + ((Spectateur)obj).ID, connection));
                        builder = new SqlCommandBuilder(adapter);

                        dt = DataTableGeneric.BuildDataTableByRequestForSpectator("SELECT " + generic.ValuesSpectateur() + " FROM " + generic.FromSpectateur() + " WHERE ID = " + ((Spectateur)obj).ID);
                        if (dt.Rows.Count == 1) 
                        {
                            dt.Rows[0]["NOM"] = ((Spectateur)obj).Nom;
                            dt.Rows[0]["PRENOM"] = ((Spectateur)obj).Prenom;
                            dt.Rows[0]["DATENAISSANCE"] = ((Spectateur)obj).DateNaissance;
                            dt.Rows[0]["ADRESSE"] = ((Spectateur)obj).Adresse;
                            dt.Rows[0]["EMAIL"] = ((Spectateur)obj).Email;
                            finish = true;
                        }
                        #endregion
                        break;

                    default:
                        return 0;
                }

                // Si la condition dans le case est passé
                if (finish) 
                {
                    adapter.InsertCommand = builder.GetInsertCommand();
                    adapter.UpdateCommand = builder.GetUpdateCommand();
                    adapter.DeleteCommand = builder.GetDeleteCommand();

                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                    nbLigne = adapter.Update(dt);
                }

                return finish ? nbLigne : 0;
            }
        }
        #endregion

        #region DELETE
        /// <summary>
        /// Requête Update sur une des tables de la base de données
        /// </summary>
        /// <param name="obj"> Objet à mettre à jour en base de donnée</param>
        /// <param name="nomTable">Nom de la table de la base de donnée</param>
        /// <returns></returns>
        public int Delete(int ID, String nomTable)
        {
            int nbLigne = 0;
            String request = String.Empty;

            DataTableGeneric generic = new DataTableGeneric();
            SqlDataAdapter adapter;
            SqlCommandBuilder builder;
            DataTable dt;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                switch (nomTable.ToUpper())
                {
                    case "COUPE":
                        #region Coupe
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesCoupe() + " FROM " + generic.FromCoupe() + " WHERE ID = " + ID, connection));
                        builder = new SqlCommandBuilder(adapter);
                        dt = DataTableGeneric.BuildDataTableByRequestForCoupe("SELECT " + generic.ValuesCoupe() + " FROM " + generic.FromCoupe() + " WHERE ID = " + ID);
                        #endregion
                        break;

                    case "PLAYER":
                        #region Joueur
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesPlayer() + " FROM " + generic.FromPlayer() + " WHERE ID = " + ID, connection));
                        builder = new SqlCommandBuilder(adapter);
                        dt = DataTableGeneric.BuildDataTableByRequestForPlayer("SELECT " + generic.ValuesPlayer() + " FROM " + generic.FromPlayer() + " WHERE ID = " + ID);
                        #endregion
                        break;

                    case "EQUIPE":
                        #region Equipe
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesEquipe() + " FROM " + generic.FromEquipe() + " WHERE ID = " + ID, connection));
                        builder = new SqlCommandBuilder(adapter);
                        dt = DataTableGeneric.BuildDataTableByRequestForTeams("SELECT " + generic.ValuesEquipe() + " FROM " + generic.FromEquipe() + " WHERE ID = " + ID);
                        #endregion
                        break;

                    case "STADE":
                        #region Stade
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesStade() + " FROM " + generic.FromStade() + " WHERE ID = " + ID, connection));
                        builder = new SqlCommandBuilder(adapter);
                        dt = DataTableGeneric.BuildDataTableByRequestForStade("SELECT " + generic.ValuesStade() + " FROM " + generic.FromStade() + " WHERE ID = " + ID);
                        #endregion
                        break;

                    case "RESERVATION":
                        #region Reservation
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesReservation() + " FROM " + generic.FromReservation() + " WHERE ID = " + ID, connection));
                        builder = new SqlCommandBuilder(adapter);
                        dt = DataTableGeneric.BuildDataTableByRequestForReservation("SELECT " + generic.ValuesReservation() + " FROM " + generic.FromReservation() + " WHERE ID = " + ID);
                        #endregion
                        break;

                    case "MATCH":
                        #region Match
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesMatch() + " FROM " + generic.FromMatch() + " WHERE ID = " + ID, connection));
                        builder = new SqlCommandBuilder(adapter);
                        dt = DataTableGeneric.BuildDataTableByRequestForMatch("SELECT " + generic.ValuesMatch() + " FROM " + generic.FromMatch() + " WHERE ID = " + ID);
                        #endregion
                        break;

                    case "SPECTATEUR":
                        #region Spectateur
                        adapter = new SqlDataAdapter(new SqlCommand("SELECT " + generic.ValuesSpectateur() + " FROM " + generic.FromSpectateur() + " WHERE ID = " + ID, connection));
                        builder = new SqlCommandBuilder(adapter);
                        dt = DataTableGeneric.BuildDataTableByRequestForSpectator("SELECT " + generic.ValuesSpectateur() + " FROM " + generic.FromSpectateur() + " WHERE ID = " + ID);
                        #endregion
                        break;

                    default:
                        return 0;
                }

                if (dt.Rows.Count == 1)
                {
                    dt.Rows[0].Delete();

                    adapter.InsertCommand = builder.GetInsertCommand();
                    adapter.UpdateCommand = builder.GetUpdateCommand();
                    adapter.DeleteCommand = builder.GetDeleteCommand();

                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                    nbLigne = adapter.Update(dt);
                }

                return nbLigne;
            }
        }
        #endregion

        #region Read

        /// <summary>
        /// Construction d'une Coupe à partir des données d'une ligne de la table Coupe
        /// </summary>
        /// <param name="reader"> SqlDataReader </param>
        /// <returns> La coupe construite </returns>
        private Coupe ReadCup(SqlDataReader reader)
        {
            return new Coupe(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2));
        }

        /// <summary>
        /// Construction d'un Joueur à partir des données d'une ligne de la table Player
        /// </summary>
        /// <param name="reader"> SqlDataReader </param>
        /// <returns> Le joueur construite </returns>
        private Joueur ReadPlayer(SqlDataReader reader)
        {
            return new Joueur(reader.GetInt32(5), (PosteJoueur)reader.GetInt32(4), reader.GetInt32(6),
                reader.GetDateTime(3), reader.GetString(1), reader.GetString(2), reader.GetInt32(0), reader.GetInt32(7));
        }

        /// <summary>
        /// Construction d'une Equipe à partir des données d'une ligne de la table Equipe
        /// </summary>
        /// <param name="reader"> SqlDataReader </param>
        /// <returns> L'équipe construite avec la liste de ses joueurs </returns>
        private Equipe ReadEquipe(SqlDataReader reader)
        {
            int idEquipe = reader.GetInt32(0);
            List<Joueur> joueurs = Select("Select * from Player where EquipeId = " + idEquipe, ReadPlayer).ConvertAll(o => o as Joueur);
            return new Equipe(joueurs, reader.GetString(1), reader.GetString(2), reader.GetInt32(0));
        }

        /// <summary>
        /// Construction d'un Stade à partir des données d'une ligne de la table Stade
        /// </summary>
        /// <param name="reader"> SqlDataReader </param>
        /// <returns> Le Stade construit </returns>
        private Stade ReadStade(SqlDataReader reader)
        {
            return new Stade(reader.GetString(2), reader.GetString(1), reader.GetInt32(3), reader.GetDouble(4), reader.GetInt32(0));
        }

        /// <summary>
        /// Construction d'un Match à partir des données d'une ligne de la table Match
        /// </summary>
        /// <param name="reader"> SqlDataReader </param>
        /// <returns> Le Match construit avec les Equipe et le Stade correspondants </returns>
        private Match ReadMatch(SqlDataReader reader)
        {
            Equipe equipeDom = Select("Select * from Equipe where Id = " + reader.GetInt32(4), ReadEquipe).ConvertAll(o => o as Equipe).First();
            Equipe equipeVis = Select("Select * from Equipe where Id = " + reader.GetInt32(5), ReadEquipe).ConvertAll(o => o as Equipe).First();
            Stade stade = Select("Select * from Stade where Id = " + reader.GetInt32(8), ReadStade).ConvertAll(o => o as Stade).First();

            return new Match(reader.GetInt32(3), reader.GetDateTime(1), equipeDom, equipeVis, reader.GetDouble(2), reader.GetInt32(6), reader.GetInt32(7), stade, reader.GetInt32(0));
        }

        /// <summary>
        /// Construction d'un Spectateur à partir des données d'une ligne de la table Spectateur
        /// </summary>
        /// <param name="reader"> SqlDataReader </param>
        /// <returns> Le Spectateur construit </returns>
        private Spectateur ReadSpectateur(SqlDataReader reader)
        {
            return new Spectateur(reader.GetString(1), reader.GetString(2), reader.GetString(4), reader.GetString(5), reader.GetDateTime(3), reader.GetInt32(0));
        }

        /// <summary>
        /// Construction d'une Reservation à partir des données d'une ligne de la table Reservation
        /// </summary>
        /// <param name="reader"> SqlDataReader </param>
        /// <returns> La Reservation construite avec ses Match et Spectateur correspondants </returns>
        private Reservation ReadReservation(SqlDataReader reader)
        {
            Match match = Select("Select * from Match where Id = " + reader.GetInt32(1), ReadMatch).ConvertAll(o => o as Match).First();
            Spectateur spectateur = Select("Select * from Spectateur where Id = " + reader.GetInt32(3), ReadSpectateur).ConvertAll(o => o as Spectateur).First();

            return new Reservation(reader.GetInt32(2), match, reader.GetInt32(4), spectateur, reader.GetInt32(0));
        }

        #endregion
    }
}
